﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetRightInPersonToHandleClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetRightInPersonToHandleClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetRightInPersonToHandleClass] 
    @ClassID int,
    @UserName nvarchar(50),
    @Act_ID int,
    @AllowAccess bit output
/*

======================================================================
功能:    得到个人@UserName是否能对@ClassID组进行@Act_ID操作
参数:
    @ClassID int             :    组（类）ID
    @UserName nvarchar(255)    :    用户名
    @Act_ID int            :    权利代号

备注:    继承指是否继承在父组中的权利(在父组中有权利即在此组中有权利)
======================================================================

*/
AS
BEGIN
SET NOCOUNT ON

SET @AllowAccess = 0;

--得到个人@UserName是否能对组@ClassID进行@Act_ID操作
If EXISTS(SELECT 1 
    FROM 
        UDS_Assign_Rule a,
        uds_staff b
    WHERE     
        a.staff_id = b.staff_id 
        and b.staff_name = @username
        and A.act_id = @act_id 
        and a.based_on = 4 /* staff */ 
        and a.team_id= 0
    ) 
BEGIN
    SET @AllowAccess = 1;
    RETURN;
END

--得到所有被访问组的父组（包括自己）
DECLARE @pteam TABLE (pid int);

with Class_ParentTree ( ClassID )
as
(
    select @ClassID    

    union all    

    select c.ClassParentID
    from Class_ParentTree p inner join dbo.UDS_Class c on p.ClassID = c.ClassID and c.ClassID <> c.ClassParentID
)
insert into @pteam ( pid )
select ClassID
from Class_ParentTree;

IF EXISTS(
        SELECT 1 
            FROM 
                dbo.UDS_Assign_Rule a,
                dbo.uds_staff b    
            WHERE       
                a.Staff_ID = b.Staff_ID
                and b.Staff_Name = @username
                and a.act_id = @act_id
                and a.based_on = 4 /* staff */
                and a.team_id IN (SELECT pid FROM @pteam) )
    SET @AllowAccess = 1;

END
GO